package foo.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import foo.base.BaseJdbcDAO;
import foo.dbvo.ProjDbvo;
import foo.dbvo.WbsDbvo;

@Repository
public class ProjDAO extends BaseJdbcDAO {
	public List<Map<String, Object>> findProjDropDownList(String userId) {
		List<Map<String, Object>> list = jdbcTemplate
				.queryForList("SELECT sn sn, name name  FROM proj ORDER BY name");
		return list;
	}

	public List<ProjDbvo> findProjList() {
		StringBuilder sql = coreFindProjSQL();
		String s = sql.toString();
		logger.debug("findProjListByPk SQL:" + s);
		ParameterizedBeanPropertyRowMapper<ProjDbvo> pbprm = ParameterizedBeanPropertyRowMapper
				.newInstance(ProjDbvo.class);
		List<ProjDbvo> list = jdbcTemplate.query(s, pbprm);
		return list;
	}

	private StringBuilder coreFindProjSQL() {
		StringBuilder sql = new StringBuilder(
				" SELECT t.sn, t.name, t.content ");
		sql.append(" FROM proj t ");
		return sql;
	}

	public ProjDbvo findProjByPk(Integer sn) {
		StringBuilder sql = coreFindProjSQL();
		sql.append(" WHERE t.sn =? ");
		String s = sql.toString();
		logger.debug("findProjListByPk SQL:" + s);
		ParameterizedBeanPropertyRowMapper<ProjDbvo> pbprm = ParameterizedBeanPropertyRowMapper
				.newInstance(ProjDbvo.class);
		ProjDbvo dbvo = jdbcTemplate.queryForObject(s, new Object[] { sn },
				pbprm);
		return dbvo;
	}

	/** @return PK 序號 */
	public Integer insertProj(String name, String content) throws Exception {
		Integer sn = getSn();
		String sql = " INSERT INTO proj(sn, name, content) VALUES(?,?,?)";
		jdbcTemplate.update(sql, sn, name, content);
		return sn;
	}

	public void updateProj(String sn, String name, String dscr) {
		String sql = " UPDATE proj SET NAME = ?, content = ? WHERE sn = ?";
		jdbcTemplate.update(sql, name, dscr, sn);
	}

	public WbsDbvo findWbsByPk(Integer sn) {
		StringBuilder sql = new StringBuilder(
				" SELECT t.sn, t.content, t.est_hr, t.name, ");
		sql.append("t.proj_sn, t.status, t.type_id , ");
		sql.append("get_dscr(t.type_id)  typeDscr ");
		sql.append("FROM  wbs t WHERE t.sn =? ");
		ParameterizedBeanPropertyRowMapper<WbsDbvo> pbprm = ParameterizedBeanPropertyRowMapper
				.newInstance(WbsDbvo.class);
		String sqlStr = sql.toString();
		logger.debug("Query SQL:" + sqlStr);
		WbsDbvo wbsdbvo = jdbcTemplate.queryForObject(sqlStr,
				new Object[] { sn }, pbprm);
		return wbsdbvo;

	}

	/** 查詢WBS; content、typeId可傳null */
	public List<WbsDbvo> findWbsByQuery(String name, String typeId,
			Integer projSn) {
		ParameterizedBeanPropertyRowMapper<WbsDbvo> pbprm = ParameterizedBeanPropertyRowMapper
				.newInstance(WbsDbvo.class);
		StringBuilder sql = new StringBuilder(
				" SELECT t.sn, t.name, t.type_id, ");
		sql.append(" get_dscr(t.type_id) typeDscr, t.est_hr, t.proj_sn ");
		sql.append(" FROM wbs t ");
		sql.append(" WHERE t.proj_sn =? ");
		ArrayList<Object> params = new ArrayList<Object>();
		params.add(projSn);
		if (StringUtils.isNotBlank(typeId)) {
			sql.append(" AND t.type_id=? ");
			params.add(typeId);
		}
		if (StringUtils.isNotBlank(name)) {
			sql.append(" AND t.name LIKE ? ");
			params.add("%" + name + "%");
		}
		String sqlS = sql.toString();
		logger.debug("QUERY SQL:" + sqlS);
		List<WbsDbvo> list = jdbcTemplate.query(sqlS, params.toArray(), pbprm);
		return list;
	}

	public void updateWbs(Integer sn, String name, String typeId, Integer estHr) {
		StringBuilder sql = new StringBuilder(
				" UPDATE wbs SET name =?, type_id =?,  est_hr =?  ");
		sql.append(" WHERE sn=? ");
		String sqlStr = sql.toString();
		logger.debug("UPDATE SQL:" + sqlStr);
		jdbcTemplate.update(sqlStr, new Object[] { name, typeId, estHr, sn });
	}

	public void deleteWbs(Integer sn) {
		String sql = " DELETE FROM wbs WHERE sn =?";
		jdbcTemplate.update(sql, new Object[] { sn });
	}

	public void insertWbs(Integer projSn, String name, String typeId,
			Integer estHr) {
		StringBuilder sql = new StringBuilder(" INSERT INTO wbs ");

		sql.append(" (sn, proj_sn, type_id, ");
		sql.append(" name, est_hr) ");

		sql.append(" VALUES (?, ?, ?, ");
		sql.append(" ?, ?) ");
		String s = sql.toString();
		logger.debug("INSERT SQL:" + s);
		jdbcTemplate.update(s, new Object[] { getSn(), projSn, typeId, name,
				estHr });

	}

}
